package com.wwwc.index.ejb.forum;

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.rmi.RemoteException;

import com.wwwc.index.servlet.ForumDetails;

public class ForumBean implements SessionBean {
    private Connection con;

    public void ejbCreate() throws CreateException {}
    public void ejbRemove() {}
    public void ejbActivate() {}
    public void ejbPassivate() {}
    public void setSessionContext(SessionContext sc) {}

    public int tableExists(String table_name) {
	int found = 0;
        try {  
	    makeConnection();
	    String query = "SELECT TABLE_NAME FROM SYSTEM_TABLES WHERE TABLE_NAME='"+table_name+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    while (rs.next()) {
		if((rs.getString(1)).equals(table_name)) {
		    found = 1;
		}
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    found = -1;
	    System.out.println("ForumBean:tableExists:Error:"+e.getMessage());
        } 
	return found;
    } 

    public String createForumTable(String query) {
        try {  
	    makeConnection();
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    return(e.getMessage());
        } 
	return "Excuting :"+query+"....\n Result:OK";
    } 

    public int removeSingleForum(String table_name, String fid) {
	int result = -1;
        try {	  
	    makeConnection();
	    String query = "UPDATE "+table_name+" SET REMOVED = ? WHERE ID="+fid+";";
	
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setBoolean(1, true);
	    result = prepStmt.executeUpdate();

	    if(result != 1) {
		System.out.println("ForumBean:removeSingleForum:Error:"+query);
	    }

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:removeSinglForum:error:" + e.getMessage());
        } 
	return result;
    }

    public int deleteSingleForum(String table_name, String fid) {
	int result = -1;
        try {	  
	    makeConnection();
	    String query = "DELETE FROM "+table_name+" WHERE ID="+fid+";";
	
	    PreparedStatement prepStmt = con.prepareStatement(query);

	    result = prepStmt.executeUpdate();

	    if(result != 1) {
		System.out.println("ForumBean:deletSingleForum:Error:"+query);
	    }

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:deleteSinglForum:error:" + e.getMessage());
        } 
	return result;
    }

    public String addNewForum(String table_name, String directory_id, int group_id, int position, int parent_id, 
			      int total_reply, String path, int size, int readers, float rating, java.util.Date date, 
			      String title, String user, 
			      String text, String image, String link, boolean allow_reply, boolean on_top,
			      boolean removed, String user_ip) {
	
	String error = null;
	int file_id = getNextForumId(table_name);

	if(parent_id == -1) {
	    group_id  = getNextForumGroupId(table_name, directory_id);
	    parent_id = file_id;
	    position  = 0;
	    path = ""+file_id;
	}
	else {
	    int total_parent_reply = getForumDetailsTotalParentReply(table_name, parent_id);
	    updateForumDetailsTotalParentReply(table_name, parent_id, (1+total_parent_reply));
	    path = path+"-"+(total_parent_reply+1);
	}

	String query = "INSERT INTO "+table_name+
	    "(ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID, TOTAL_REPLY, PATH, SIZE, READERS, RATING, DATE, TITLE, USER,"+
	    "TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP) VALUES"+
	    "(?,    ?,    ?,    ?,    ?,    ?,    ?,     ?,     ?,    ?,"+
	    " ?,    ?,    ?,    ?,    ?,    ?,    ?,     ?,     ?,    ?);";

	try {
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setInt(1, file_id);
	    prepStmt.setString(2, directory_id);
	    prepStmt.setInt(3, group_id);
	    prepStmt.setInt(4, position);
	    prepStmt.setInt(5, parent_id);
	    prepStmt.setInt(6, total_reply);
	    prepStmt.setString(7, path);
	    prepStmt.setInt(8, size);
	    prepStmt.setInt(9, readers);
	    prepStmt.setFloat(10, rating);
	    prepStmt.setDate(11, (new java.sql.Date(date.getTime())));
	    prepStmt.setString(12, title);
	    prepStmt.setString(13, user);
	    prepStmt.setString(14, text);
	    prepStmt.setString(15, image);
	    prepStmt.setString(16, link);

	    prepStmt.setBoolean(17, allow_reply);
	    prepStmt.setBoolean(18, on_top);
	    prepStmt.setBoolean(19, removed);
	    prepStmt.setString(20, user_ip);

	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
	}
	catch (Exception e) {
	    error = "ForumBean:addNewForum:error:" + e.getMessage();
	    System.out.println(error);
        } 
	return error;
    }

    public String updateSingleForumDetails(String table_name, String fid, String readers, 
					   String rating, String date, String title, String user, 
					   String text, String image, String link, String allow_reply, String on_top,
					   String removed) {
	PreparedStatement prepStmt = null;
	String error = null;
	try {	  
	    makeConnection();
	    String query = "UPDATE "+table_name+" SET READERS = ?, RATING = ?, DATE = ?, TITLE = ?, USER = ?,"+
		" TEXT = ?, IMAGE = ?, LINK = ?, ALLOW_REPLY = ?, ON_TOP =?, REMOVED = ?  WHERE ID = ?;";
	    prepStmt = con.prepareStatement(query);
	    prepStmt.setInt(1, Integer.parseInt(readers));
	    prepStmt.setFloat(2, Float.parseFloat(rating));
	    prepStmt.setDate(3, java.sql.Date.valueOf(date));
	    prepStmt.setString(4, title);
	    prepStmt.setString(5, user);
	    prepStmt.setString(6, text);
	    prepStmt.setString(7, image);
	    prepStmt.setString(8, link);
	    prepStmt.setBoolean(9, (Boolean.valueOf(allow_reply)).booleanValue());
	    prepStmt.setBoolean(10, (Boolean.valueOf(on_top)).booleanValue());
	    prepStmt.setBoolean(11, (Boolean.valueOf(removed)).booleanValue());
	    prepStmt.setInt(12, Integer.parseInt(fid));

	    if(1 != prepStmt.executeUpdate()) {
		error = "Update error.";
	    }

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    error = e.getMessage();
	    System.out.println("ForumBean:updateSingleForumDetails:error:" + e.getMessage());
        } 
	return error;
    }

    private int getNextForumGroupId(String table_name, String directory_id) {
	int group_id = -1;
        try {	  
	    makeConnection();
	    String query = "SELECT MAX(GROUP_ID) AS max_gid FROM "+table_name+" WHERE DIRECTORY_ID='"+directory_id+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if(rs.next()) {
		group_id = 1+rs.getInt("max_gid");
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:getNextForumGroupId:error:" + e.getMessage());
        } 
	return group_id;
    } 

    private int getForumDetailsTotalParentReply(String table_name, int fid) {
	int total_reply = 0;
        try {	  
	    makeConnection();
	    String query = "SELECT TOTAL_REPLY AS treply FROM "+table_name+" WHERE ID='"+fid+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if(rs.next()) {
		total_reply = rs.getInt("treply");
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:getForumDetailTotalParentReply:error:" + e.getMessage());
        } 
	return total_reply;
    } 

    public int updateForumDetailsTotalParentReply(String table_name, int fid, int total_reply) {
	int result = -1;
	try {	  
	    makeConnection();
	    String query = "UPDATE ? SET TOTAL_REPLY = ? WHERE ID = ?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setString(1, table_name);
	    prepStmt.setInt   (2, total_reply);
	    prepStmt.setInt   (3, fid);

	    result = prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:getForumDetailTotalParentReply:error:" + e.getMessage());
        } 
	return result;
    } 

    public int getForumDetailsReaders(String table_name, int fid) {
	int readers = -1;
	try {	  
	    makeConnection();
	    String query = "SELECT READERS AS current_readers FROM ? WHERE ID = ?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setString(1, table_name);
	    prepStmt.setInt   (2, fid);

	    ResultSet rs = prepStmt.executeQuery();

	    if(rs.next()) {
		readers = rs.getInt("current_readers");
	    }

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:getForumDetailsReaders:error:" + e.getMessage());
        } 
	return readers;
    } 

    public int updateForumDetailsReaders(String table_name, String fid) {
	return (updateForumDetailsReaders(table_name, Integer.parseInt(fid)));
    }

    public int updateForumDetailsReaders(String table_name, int fid) {
	int result = -1;
	int readers = getForumDetailsReaders(table_name, fid);
	if(readers == -1) {
	    return -1;
	}

	try {	  
	    makeConnection();
	    String query = "UPDATE ? SET READERS = ? WHERE ID = ?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setString(1, table_name);
	    prepStmt.setInt   (2, 1+readers);
	    prepStmt.setInt   (3, fid);

	    result = prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("ForumBean:updateForumDetailsReaders:error:" + e.getMessage());
        } 
	return result;
    } 

    public ArrayList getForumDetailsList(String table_name, String dir_id, int from, int rows, boolean show_remove) {
	ArrayList a  = new ArrayList();
	ForumDetails fd = null;

        try {
	    makeConnection();
	    String query1 = "SELECT LIMIT "+from+" "+rows+" ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID,"+
		" TOTAL_REPLY, PATH, SIZE, READERS,"+
		" RATING, DATE, TITLE,"+
		" USER, TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP FROM "+
		table_name+" WHERE DIRECTORY_ID = '"+dir_id+"'"+" ";

	    String query = null;
	    if(show_remove) {
		query = query1+" ORDER BY ON_TOP DESC, GROUP_ID DESC, PATH;";
	    }
	    else {
		query = query1+ " AND REMOVED='false' ORDER BY ON_TOP DESC, GROUP_ID DESC, PATH;";
	    }

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    int fid;
	    String did;
	    int gid;
	    int position;
	    int parentId;
	    int totalReply;
	    String path;
	    int size;
	    int readers;
	    float rating;
	    java.util.Date timeStamp;
	    String title;
	    String user;
	    String text;
	    String image;
	    String link;
	    boolean allowReply;
	    boolean onTop;
	    boolean removed;
	    String userIp;

	    while (rs.next()) {
		fid            = rs.getInt(1);
		did            = rs.getString(2);
		gid            = rs.getInt(3);
		position       = rs.getInt(4);
		parentId       = rs.getInt(5);
		totalReply     = rs.getInt(6);
		path           = rs.getString(7);
		size           = rs.getInt(8);
		readers        = rs.getInt(9);
		rating         = rs.getFloat(10);
		timeStamp      = rs.getDate(11);
		title          = rs.getString(12);
		user           = rs.getString(13);
		text           = rs.getString(14);
		image          = rs.getString(15);
		link           = rs.getString(16);
		allowReply     = rs.getBoolean(17);
		onTop          = rs.getBoolean(18);
		removed        = rs.getBoolean(19);
		userIp         = rs.getString(20);

		fd = new ForumDetails(fid, did, gid, position, parentId,  totalReply, path, size, readers, rating, timeStamp, title,
				      user, text, image, link, allowReply, onTop, removed, userIp);
		a.add(fd);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getForumDetailsList:error:" + e.getMessage());
        } 

        return a;
    } 

    public ArrayList getSearchResultList(String table_name, String dir_id, int from, int rows, boolean show_remove, 
					 String search_string) {
	ArrayList a  = new ArrayList();
	if(search_string == null || search_string.length()==0) {
	    return a;
	}

	ForumDetails fd = null;
	String search_query = getSearchQuery(search_string);

        try {
	    makeConnection();
	    String query1 = "SELECT LIMIT "+from+" "+rows+" ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID,"+
		" TOTAL_REPLY, PATH, SIZE, READERS,"+
		" RATING, DATE, TITLE,"+
		" USER, TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP FROM "+
		table_name+" WHERE DIRECTORY_ID = '"+dir_id+"'"+" ";

	    String query = null;
	    if(show_remove) {
		query = query1+" AND "+search_query+" ORDER BY ON_TOP DESC, GROUP_ID DESC, PATH;";
	    }
	    else {
		query = query1+ " AND REMOVED='false' AND "+search_query+" ORDER BY ON_TOP DESC, GROUP_ID DESC, PATH;";
	    }

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    int fid;
	    String did;
	    int gid;
	    int position;
	    int parentId;
	    int totalReply;
	    String path;
	    int size;
	    int readers;
	    float rating;
	    java.util.Date timeStamp;
	    String title;
	    String user;
	    String text;
	    String image;
	    String link;
	    boolean allowReply;
	    boolean onTop;
	    boolean removed;
	    String userIp;

	    while (rs.next()) {
		fid            = rs.getInt(1);
		did            = rs.getString(2);
		gid            = rs.getInt(3);
		position       = rs.getInt(4);
		parentId       = rs.getInt(5);
		totalReply     = rs.getInt(6);
		path           = rs.getString(7);
		size           = rs.getInt(8);
		readers        = rs.getInt(9);
		rating         = rs.getFloat(10);
		timeStamp      = rs.getDate(11);
		title          = rs.getString(12);
		user           = rs.getString(13);
		text           = rs.getString(14);
		image          = rs.getString(15);
		link           = rs.getString(16);
		allowReply     = rs.getBoolean(17);
		onTop          = rs.getBoolean(18);
		removed        = rs.getBoolean(19);
		userIp         = rs.getString(20);

		fd = new ForumDetails(fid, did, gid, position, parentId,  totalReply, path, size, readers, rating, timeStamp, title,
				      user, text, image, link, allowReply, onTop, removed, userIp);
		a.add(fd);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getForumDetailsList:error:" + e.getMessage());
        } 

        return a;
    } 

    public Object getSingleForumDetails(String table_name, String id) {
	ForumDetails fd = null;

        try {
	    makeConnection();
	    String query = "SELECT ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID,"+
		" TOTAL_REPLY, PATH, SIZE, READERS,"+
		" RATING, DATE, TITLE,"+
		" USER, TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP FROM "+
		table_name+" WHERE ID = "+id+";";

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    int fid;
	    String did;
	    int gid;
	    int position;
	    int parentId;
	    int totalReply;
	    String path;
	    int size;
	    int readers;
	    float rating;
	    java.util.Date timeStamp;
	    String title;
	    String user;
	    String text;
	    String image;
	    String link;
	    boolean allowReply;
	    boolean onTop;
	    boolean removed;
	    String userIp;

	    while (rs.next()) {
		fid            = rs.getInt(1);
		did            = rs.getString(2);
		gid            = rs.getInt(3);
		position       = rs.getInt(4);
		parentId       = rs.getInt(5);
		totalReply     = rs.getInt(6);
		path           = rs.getString(7);
		size           = rs.getInt(8);
		readers        = rs.getInt(9);
		rating         = rs.getFloat(10);
		timeStamp      = rs.getDate(11);
		title          = rs.getString(12);
		user           = rs.getString(13);
		text           = rs.getString(14);
		image          = rs.getString(15);
		link           = rs.getString(16);
		allowReply     = rs.getBoolean(17);
		onTop          = rs.getBoolean(18);
		removed        = rs.getBoolean(19);
		userIp         = rs.getString(20);

		fd = new ForumDetails(fid, did, gid, position, parentId,  totalReply, path, size, readers, rating, timeStamp, title,
				      user, text, image, link, allowReply, onTop, removed, userIp);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getSingleForumDetails:error:" + e.getMessage());
        } 

        return (Object)fd;
    } 

    /*
    public Hashtable getForumDetailsListHashtable(String table_name, String dir_id) {
	Hashtable hash  = new Hashtable();
	ForumDetails fd = null;

        try {
	    makeConnection();
	    String query = "SELECT ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID, SIZE, READERS, RATING, DATE, TITLE,"+
		" USER, TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP FROM "+table_name+" WHERE DIRECTORY_ID = '"+dir_id+"'"+
		" ORDER BY GROUP_ID DESC, PARENT_ID, ID;";

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    int fid;
	    String did;
	    int gid;
	    int position;
	    int parentId;
	    int size;
	    int readers;
	    float rating;
	    java.util.Date timeStamp;
	    String title;
	    String user;
	    String text;
	    String image;
	    String link;
	    boolean allowReply;
	    boolean onTop;
	    boolean removed;
	    String userIp;
	   
	    while (rs.next()) {
		fid            = rs.getInt(1);
		did            = rs.getString(2);
		gid            = rs.getInt(3);
		position       = rs.getInt(4);
		parentId       = rs.getInt(5);
		size           = rs.getInt(6);
		readers        = rs.getInt(7);
		rating         = rs.getFloat(8);
		timeStamp      = rs.getDate(9);
		title          = rs.getString(10);
		user           = rs.getString(11);
		text           = rs.getString(12);
		image          = rs.getString(13);
		link           = rs.getString(14);
		allowReply     = rs.getBoolean(15);
		onTop          = rs.getBoolean(16);
		removed        = rs.getBoolean(17);
		userIp         = rs.getString(18);

		fd = new ForumDetails(fid, did, gid, position, parentId,  size, readers, rating, timeStamp, title,
				      user, text, image, link, allowReply, onTop, removed, userIp);
		hash.put(""+fid, fd);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getForumDetailsList:error:" + e.getMessage());
        } 

        return hash;
    } 

    public Vector getForumDetailsListIds(String table_name, String dir_id) {
	Vector v  = new Vector();

        try {
	    makeConnection();
	    
	    String query = "SELECT ID FROM "+table_name+" WHERE DIRECTORY_ID='"+dir_id+"' AND PARENT_ID=0 ORDER BY ID DESC;";

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();
	   
	    while (rs.next()) {
		v.add(rs.getString(1));
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getForumDetailsList:error:" + e.getMessage());
        } 

        return v;
    } 

    public Vector getForumDetailsListVector(String table_name, String dir_id) {
	Vector v  = new Vector();
	ForumDetails fd = null;

        try {
	    makeConnection();
	    
	    String query = "SELECT ID, DIRECTORY_ID, GROUP_ID, POSITION, PARENT_ID, SIZE, READERS, RATING, DATE, TITLE,"+
		" USER, TEXT, IMAGE, LINK, ALLOW_REPLY, ON_TOP, REMOVED, USER_IP FROM "+table_name+" WHERE DIRECTORY_ID="+
		"'"+dir_id+"' ORDER BY GROUP_ID, PARENT_ID, POSITION;";
		

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    int fid;
	    String did;
	    int gid;
	    int position;
	    int parentId;
	    int size;
	    int readers;
	    float rating;
	    java.util.Date timeStamp;
	    String title;
	    String user;
	    String text;
	    String image;
	    String link;
	    boolean allowReply;
	    boolean onTop;
	    boolean removed;
	    String userIp;
	   
	    while (rs.next()) {
		fid            = rs.getInt(1);
		did            = rs.getString(2);
		gid            = rs.getInt(3);
		position       = rs.getInt(4);
		parentId       = rs.getInt(5);
		size           = rs.getInt(6);
		readers        = rs.getInt(7);
		rating         = rs.getFloat(8);
		timeStamp      = rs.getDate(9);
		title          = rs.getString(10);
		user           = rs.getString(11);
		text           = rs.getString(12);
		image          = rs.getString(13);
		link           = rs.getString(14);
		allowReply     = rs.getBoolean(15);
		onTop          = rs.getBoolean(16);
		removed        = rs.getBoolean(17);
		userIp         = rs.getString(18);

		fd = new ForumDetails(fid, did, gid, position, parentId,  size, readers, rating, timeStamp, title,
				      user, text, image, link, allowReply, onTop, removed, userIp);
		v.add(fd);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("ForumBean:getForumDetailsList:error:" + e.getMessage());
        } 

        return v;
    } 
    */

    public int getTableRowCounter(String table_name, boolean include_removed){
	int counter = -1;
	String query = null;
	try {	
	    makeConnection();

	    if(include_removed) {
		query = "SELECT COUNT(*) AS rowcount FROM "+table_name+";";
	    }
	    else {
		query = "SELECT COUNT(*) AS rowcount FROM "+table_name+" WHERE REMOVED='false';";
	    }

	    PreparedStatement prepStmt =  con.prepareStatement(query);

	    ResultSet rs = prepStmt.executeQuery();
	    if(rs.next()) {
		counter = rs.getInt("rowcount");
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    System.out.println("EJB:ForumBean:getForumRowCounter="+e+"\n"+query);
	    return -1;
	}
	catch(Exception e) {
	    System.out.println("EJB:ForumBEan:getForumRowCounter="+e+"\n"+query);
	    return -1;
	}

        return counter;
    }

    public int getSearchResultRowCounter(String table_name, boolean include_removed, String search_string) {
	int counter = 0;
	if(search_string == null || search_string.length()==0) {
	    return 0;
	}
	
	String query = null;
	try {
	    	
	    makeConnection();
	    
	    if(include_removed) {
		query = "SELECT COUNT(*) AS rowcount FROM "+table_name+" WHERE "+getSearchQuery(search_string)+";";
	    }
	    else {
		query = "SELECT COUNT(*) AS rowcount FROM "+table_name+" WHERE REMOVED='false' AND "+getSearchQuery(search_string)+";";
	    }

	    PreparedStatement prepStmt =  con.prepareStatement(query);

	    ResultSet rs = prepStmt.executeQuery();
	    if(rs.next()) {
		counter = rs.getInt("rowcount");
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    System.out.println("EJB:ForumBean:getSearchResultRowCounter="+e+"\n"+query);
	}
	catch(Exception e) {
	    System.out.println("EJB:ForumBEan:getSearchResultRowCounter="+e+"\n"+query);
	}

        return counter;
    }

    private String getSearchQuery (String search_string) {
	String field[] = {"TITLE", "TEXT"};
	StringBuffer qsbf = new StringBuffer("(");

	if(search_string.startsWith("\"") && search_string.endsWith("\"")) {
	    search_string = search_string.substring(1, search_string.length()-1);
	    for (int i=0; i<field.length;i++) {
		qsbf.append(field[i]+" LIKE '%"+search_string);
		if(i == field.length-1) {
		    qsbf.append("%')");
		}
		else {
		    qsbf.append("%' OR ");
		}
	    }
	}
	else {
	    if(search_string.indexOf(" ") != -1) {
		search_string = search_string.replaceAll(" ", "%' OR FIELD LIKE '%");
		for (int i=0; i<field.length;i++) {
		    qsbf.append(field[i]+" LIKE '%"+search_string.replaceAll("FIELD", field[i])+"%'");
		    if(i == field.length-1) {
			qsbf.append(")");
		    }
		    else {
			qsbf.append(" OR ");
		    }
		}
	    }
	    else {
		for (int i=0; i<field.length;i++) {
		    qsbf.append(field[i]+" LIKE '%"+search_string+"%'");
		    if(i == field.length-1) {
			qsbf.append(")");
		    }
		    else {
			qsbf.append(" OR ");
		    }
		}
	    }
	}
	System.out.println("BBBBBBBBB="+qsbf.toString());
	return qsbf.toString();
    }

    private int getNextForumId(String table_name){
	int next_id = -1;
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement("SELECT MAX(ID) AS next_id FROM "+table_name+";");
	    ResultSet rs = prepStmt.executeQuery();
	    if(rs.next()) {
		next_id = 1+rs.getInt("next_id");
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    System.out.println("getNextForumId="+e);
	    return -1;
	}
	catch(Exception e) {
	    System.out.println("getNextForumId="+e);
	    return -1;
	}

        return next_id;
    }

    ////////////////////////////////////////////////////////////////////
    // Database Routines
    ////////////////////////////////////////////////////////////////////

    private void makeConnection() {
        try {
	    InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/DefaultDS");
            con =  ds.getConnection();
        } catch (Exception e) {
	    releaseConnection();
	    System.out.println("EJB:CategoryBean: makeConnection error:" + e.getMessage());
        }
    }
  
    private void releaseConnection() {
        try {
            con.close();
        } catch (SQLException e) {
	    System.out.println("CategoryBean: Reaease connect error: " + e.getMessage());
        }
    }
}
